package gentle.service.impl;

import gentle.entity.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import gentle.service.IDoctorDetailsService;

import java.math.BigDecimal;
import java.util.List;

import gentle.base.SqlMapper;
import gentle.util.ToolUtil;
import gentle.exception.BussinessException;
import gentle.exception.BizExceptionEnum;
import gentle.exception.DateTimeKit;
import gentle.base.StringUtil;

@Service
@Transactional
public class DoctorDetailsServiceImpl implements IDoctorDetailsService {

    @Autowired
    private SqlMapper sqlMapper;

    public DoctorLookedInfo selectOneById(String id, String abbreviation) {
        String sql = "select " +
                " (CASE t2.customer_sex  WHEN 1 THEN '男' WHEN 2 THEN '女' ELSE '未知' END ) as sex ," +
                " t2.* from " + abbreviation + "customer_info t2 " +
                " where id= '" + id + "' limit 0,1;";
        DoctorLookedInfo info = sqlMapper.selectOne(sql, DoctorLookedInfo.class);
        return info;
    }

    public List<CaseInfo> medicalRecord(String reservationsId, String areaAbbreviation) {
        //病例记录
        //1、先查询当前客户的所有病例记录
        String caseInfoSql = "select left(t1.finnally_time,19) as finnallyTime1,t1.* from case_info t1  where t1.reservations_id='" + reservationsId + "'order by t1.finnally_time desc";
        List<CaseInfo> caseInfoList = sqlMapper.selectList(caseInfoSql, CaseInfo.class);
        for (CaseInfo infos : caseInfoList) {
            if (infos != null) {
                if (infos.getOperationTime() != "" && infos.getOperationTime() != null) {
                    infos.setOperationTime(infos.getOperationTime().substring(0, 10));
                }
                String sql3 = "select id,department  from " + areaAbbreviation + "pay_service_grade_info where id ='" + infos.getPayServiceId() + "' limit 1";
                CaseInfo caseInfo1 = sqlMapper.selectOne(sql3, CaseInfo.class);
                if (caseInfo1 == null) {
                    continue;
                }
                infos.setDepartment(caseInfo1.getDepartment());
            }
        }
        //2.循环遍历每个病例记录,给对应病例的牙位属性赋值
        for (CaseInfo caseInfo : caseInfoList) {
            //然后查询牙位集合
            String toothSIteSql = "select * from tooth_site t1 where t1.case_id='" + caseInfo.getId() + "' ";
            List<ToothSite> toothSiteList = sqlMapper.selectList(toothSIteSql, ToothSite.class);
            for (ToothSite toothSite : toothSiteList) {
                //根据对应的牙位类型,给病例实体的对应牙位属性赋值
                if (toothSite.getYaType().equals("1")) {
                    caseInfo.getToothSite1List().add(toothSite);
                }
                if (toothSite.getYaType().equals("2")) {
                    caseInfo.getToothSite2List().add(toothSite);
                }
                if (toothSite.getYaType().equals("3")) {
                    caseInfo.getToothSite3List().add(toothSite);
                }
                if (toothSite.getYaType().equals("4")) {
                    caseInfo.getToothSite4List().add(toothSite);
                }
                if (toothSite.getYaType().equals("5")) {
                    caseInfo.getToothSite5List().add(toothSite);
                }
            }
        }
        return caseInfoList;
    }

    public DotorCaseInfo medicalRecords(String reservationsId, String areaAbbreviation) {
        //病例记录
        //1、先查询当前客户的所有病例记录
        String caseInfoSql = "select left(t1.finnally_time,19) as finnallyTime1,t1.* from case_info t1  where t1.reservations_id='" + reservationsId + "'order by t1.finnally_time desc";
        List<CaseInfo> caseInfoList = sqlMapper.selectList(caseInfoSql, CaseInfo.class);
        for (CaseInfo infos : caseInfoList) {
            if (infos != null) {
                if (infos.getOperationTime() != "" && infos.getOperationTime() != null) {
                    infos.setOperationTime(infos.getOperationTime().substring(0, 10));
                }
                String sql3 = "select id,department from " + areaAbbreviation + "pay_service_grade_info where id ='" + infos.getPayServiceId() + "' limit 1";
                CaseInfo caseInfo1 = sqlMapper.selectOne(sql3, CaseInfo.class);
                if (caseInfo1 == null) {
                    continue;
                }
                infos.setDepartment(caseInfo1.getDepartment());
            }
        }
        //2.循环遍历每个病例记录,给对应病例的牙位属性赋值
        for (CaseInfo caseInfo : caseInfoList) {
            //然后查询牙位集合
            String toothSIteSql = "select * from tooth_site t1 where t1.case_id='" + caseInfo.getId() + "' ";
            List<ToothSite> toothSiteList = sqlMapper.selectList(toothSIteSql, ToothSite.class);
            for (ToothSite toothSite : toothSiteList) {
                //根据对应的牙位类型,给病例实体的对应牙位属性赋值
                if (toothSite.getYaType().equals("1")) {
                    caseInfo.getToothSite1List().add(toothSite);
                }
                if (toothSite.getYaType().equals("2")) {
                    caseInfo.getToothSite2List().add(toothSite);
                }
                if (toothSite.getYaType().equals("3")) {
                    caseInfo.getToothSite3List().add(toothSite);
                }
                if (toothSite.getYaType().equals("4")) {
                    caseInfo.getToothSite4List().add(toothSite);
                }
                if (toothSite.getYaType().equals("5")) {
                    caseInfo.getToothSite5List().add(toothSite);
                }
            }
        }
        DotorCaseInfo dotorCaseInfo = new DotorCaseInfo();
        dotorCaseInfo.setCaseInfoList(caseInfoList);
        return dotorCaseInfo;
    }

    public int getMedicalUpPermission(String roleName) {
        String sql = "SELECT " +
                " m1.id AS id " +
                " FROM " +
                " menu m1 " +
                " LEFT JOIN menu m2 ON m1.pcode = m2. CODE " +
                " INNER JOIN ( " +
                " SELECT " +
                " ID " +
                " FROM " +
                " menu " +
                " WHERE " +
                " ID IN ( " +
                " SELECT " +
                " menuid " +
                " FROM " +
                " relation rela " +
                " WHERE " +
                " rela.roleid=(select id from role where name='" + roleName + "' limit 1) " +
                " ) " +
                " ) m3 ON m1.id = m3.id " +
                " where m1.ismenu = 0 and m1.name='修改病历'";
        List<IdInfo> idinfoList = sqlMapper.selectList(sql, IdInfo.class);
        if (idinfoList.size() == 0) {
            return 0;
        } else {
            return 1;
        }
    }

    public DotorFollowsInfo doctorFollow(String reservationsId, String areaAbbreviation, String abbreviation) {
        DotorFollowsInfo dotorFollowsInfo = new DotorFollowsInfo();
        //病例记录
        //1、先查询当前客户的所有病例记录
        String caseInfoSql = "select left(t1.finnally_time,19) as finnallyTime1,t1.* from case_info t1  where t1.reservations_id='" + reservationsId + "'order by t1.finnally_time desc";
        List<CaseInfo> caseInfoList = sqlMapper.selectList(caseInfoSql, CaseInfo.class);
        for (CaseInfo infos : caseInfoList) {
            if (infos != null) {
                if (infos.getOperationTime() != "" && infos.getOperationTime() != null) {
                    infos.setOperationTime(infos.getOperationTime().substring(0, 10));
                }
                String sql3 = "select id,department  from " + areaAbbreviation + "pay_service_grade_info where id ='" + infos.getPayServiceId() + "' limit 1";
                CaseInfo caseInfo1 = sqlMapper.selectOne(sql3, CaseInfo.class);
                if (caseInfo1 == null) {
                    continue;
                }
                infos.setDepartment(caseInfo1.getDepartment());
            }
        }
        //2.循环遍历每个病例记录,给对应病例的牙位属性赋值
        for (CaseInfo caseInfo : caseInfoList) {
            //然后查询牙位集合
            String toothSIteSql = "select * from tooth_site t1 where t1.case_id='" + caseInfo.getId() + "' ";
            List<ToothSite> toothSiteList = sqlMapper.selectList(toothSIteSql, ToothSite.class);
            for (ToothSite toothSite : toothSiteList) {
                //根据对应的牙位类型,给病例实体的对应牙位属性赋值
                if (toothSite.getYaType().equals("1")) {
                    caseInfo.getToothSite1List().add(toothSite);
                }
                if (toothSite.getYaType().equals("2")) {
                    caseInfo.getToothSite2List().add(toothSite);
                }
                if (toothSite.getYaType().equals("3")) {
                    caseInfo.getToothSite3List().add(toothSite);
                }
                if (toothSite.getYaType().equals("4")) {
                    caseInfo.getToothSite4List().add(toothSite);
                }
                if (toothSite.getYaType().equals("5")) {
                    caseInfo.getToothSite5List().add(toothSite);
                }
            }
        }
        String doctorListSql = " select a.id as id,a.account as doctor from user a left join  dept b on a.deptid like CONCAT('%',b.id,',','%') left join role c on find_in_set(c.id,a.roleid) > 0 " +
                " where b.abbreviation='" + abbreviation + "' and (c.name='医生' or c.name='洁牙师') and a.status='1';";
        List<DoctorInfo> doctorInfoList = sqlMapper.selectList(doctorListSql, DoctorInfo.class);
        dotorFollowsInfo.setCaseInfoList(caseInfoList);
        dotorFollowsInfo.setDoctorInfoList(doctorInfoList);
        return dotorFollowsInfo;
    }

    public List<SfInfo> getSfList(String userAccount) {
        String sql = "select * from follow_template where user='" + userAccount + "'";
        List<SfInfo> list = sqlMapper.selectList(sql, SfInfo.class);
        return list;
    }

    public List<JgInfo> getJgList(String userAccount) {
        String sql = "select * from follow_results_template where user='" + userAccount + "'";
        List<JgInfo> list = sqlMapper.selectList(sql, JgInfo.class);
        return list;
    }

    public List<IconInfo> getImage(String reservationsId) {
        //获得影像信息
        String sqlIcon = "select  i.id ,i.customer_id as customerId,i.reservations_id as reservationsId,i.num as num ,i.add_time as addTime " +
                "from icon_infos i  where i.reservations_id='" + reservationsId + "' group by i.num order by num DESC";
        List<IconInfo> iconList = sqlMapper.selectList(sqlIcon, IconInfo.class);
        for (int i = 0; i < iconList.size(); i++) {
            List<IconInfo> currentList = getCurrentList(iconList.get(i).getNum(), iconList.get(i).getReservationsId());
            iconList.get(i).setCurrentList(currentList);
        }
        return iconList;
    }

    public List<IconInfo> getCurrentList(String num, String reservationsId) {
        if (ToolUtil.isEmpty(num)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String sql = "select t1.id as id,t1.outpatient_name as outpatientName,t1.add_time as addTime,t1.add_name as addName,t1.customer_name as customerName," +
                "t1.remarks as remarks,t1.image_url as imageUrl,t1.status as status,t1.score as score,t1.cause as cause,t1.num as num from icon_infos t1  where t1.num='" + num + "' and t1.reservations_id " +
                "= '" + reservationsId + "'  order by t1.icon_name1 desc";
        List<IconInfo> currentList = sqlMapper.selectList(sql, IconInfo.class);
        return currentList;
    }

    public List<DoctorRevisitInfo> followUp(String reservationsId) {
        //随访记录
        String sql4 = "select r.id,r.reservations_id,r.customer_id,r.revisit,r.revisit_time,r.outpatient_name,r.revisit_content,r.doctor,r.revisit_status," +
                "r.remarks  from return_doctor_visit_info r where r.reservations_id ='" + reservationsId + "' ";
        List<DoctorRevisitInfo> doctorRevisitInfoList = sqlMapper.selectList(sql4, DoctorRevisitInfo.class);
        return doctorRevisitInfoList;
    }

    //最近一次患者信息
    public CustomerInfo selectOneByReservationsId(String reservationsId, String abbreviation) {
        String sql = "select " +
                " (CASE t2.customer_sex  WHEN 1 THEN '男' WHEN 2 THEN '女' ELSE '未知' END ) as sex ," +
                " (CASE t2.marital_status WHEN 1 THEN '已婚' WHEN 2 THEN '未婚' WHEN 3 THEN '离异' ELSE '未知' END ) as maritalStatusName," +
                " t2.* from " + abbreviation + "customer_info t2 " +
                " where reservations_id= '" + reservationsId + "' order by arrival_time desc limit 0,1;";
        CustomerInfo customerInfo = sqlMapper.selectOne(sql, CustomerInfo.class);
        return customerInfo;
    }

    public List<ConsultantInfo> getConsultantInfoList(String reservationsId) {
        String sql = "select id as id, customer_id as customerId,left(diagnosis_time,19) as diagnosisTime," +
                "left(diagnosis_time,11) as diagnosisTimeOne,substring(diagnosis_time,12,10) as diagnosisTimeTwo," +
                "diagnosis_content as diagnosisContent,reservations_id as reservationsId,tc_name as tcName " +
                "from tc_diagnosis_info where reservations_id='" + reservationsId + "' order by diagnosis_time desc";
        List<ConsultantInfo> list = sqlMapper.selectList(sql, ConsultantInfo.class);
        return list;
    }

    public DoctorRevisitInfo lookVisitRecord(String reservationsId, String abbreviation, String outpatientName, String userAccount) {
        CustomerInfo customerInfo = this.selectOneByReservationsId(reservationsId, abbreviation);
        List<ConsultantInfo> consultantInfoList = this.getConsultantInfoList(reservationsId);
        String sqlresrvations = "select id,content from reservations_info where id='" + reservationsId + "' limit 0,1";
        ReservationsInfo reservationsInfo = sqlMapper.selectOne(sqlresrvations, ReservationsInfo.class);
        String content = null;
        if (reservationsInfo == null) {
            this.insert(customerInfo, outpatientName, userAccount);
        } else {
            content = reservationsInfo.getContent();
        }
        String tcName = "";
        String firstVisitDate = "";
        if (customerInfo != null) {
            tcName = customerInfo.getFieldTc();
            firstVisitDate = customerInfo.getFirstVisitDate();
        }
        for (ConsultantInfo info : consultantInfoList) {
            if (info.getDiagnosisTime() != "" && info.getDiagnosisTime() != null) {
                info.setDiagnosisTimeOne(info.getDiagnosisTime().substring(0, 10));
                info.setDiagnosisTimeTwo(info.getDiagnosisTime().substring(11, info.getDiagnosisTime().length()));
            }
        }
        DoctorRevisitInfo doctorRevisitInfo = new DoctorRevisitInfo();
        doctorRevisitInfo.setConsultantInfoList(consultantInfoList);
        doctorRevisitInfo.setTcName(tcName);
        doctorRevisitInfo.setFirstVisitDate(firstVisitDate);
        doctorRevisitInfo.setContent(content);
        return doctorRevisitInfo;
    }

    public List<CustomerInfo> visitRecords(String reservationsId, String outpatientName) {
        //到过的店
        String abbreviationSql = "select abbreviation from distinction_customer_status where reservations_id = '" + reservationsId + "' ";
        List<String> abbreviationList = sqlMapper.selectList(abbreviationSql, String.class);
        String customerTables = "";
        for (int i = 0; i < abbreviationList.size(); i++) {
            String head = abbreviationList.get(i);
            if (i == abbreviationList.size() - 1) {
                customerTables += "select customer_name as customerName, left(t.create_appointment_time,19) as createAppointmentTime,left(t.start_time,19) as startTime,t.id as id,t.remarks_reservation as remarksReservation," +
                        "t.reservations_id as reservationsId,t.doctor as doctor,t.field_tc as fieldTc,t.remarks as remarks,t.arrival_status as arrivalStatus," +
                        "t.archiving as archiving,t.arrival_store as arrivalStore,t.customer_status as customerStatus,t.status as status," +
                        "t.appointment_pay_service as appointmentPayService " +
                        "from " + head + "customer_info t where reservations_id='" + reservationsId + "' ";
            } else {
                customerTables += "select customer_name as customerName, left(t.create_appointment_time,19) as createAppointmentTime,left(t.start_time,19) as startTime,t.id as id,t.remarks_reservation as remarksReservation," +
                        "t.reservations_id as reservationsId,t.doctor as doctor,t.field_tc as fieldTc,t.remarks as remarks,t.arrival_status as arrivalStatus," +
                        "t.archiving as archiving,t.arrival_store as arrivalStore,t.customer_status as customerStatus,t.status as status," +
                        "t.appointment_pay_service as appointmentPayService " +
                        "from " + head + "customer_info t where reservations_id='" + reservationsId + "' union ";
            }
        }
        if (abbreviationList.size() == 0) {
            return null;
        }
        //就诊记录
        String sql11 = "select * from (" + customerTables + ") t order by t.startTime desc";
        List<CustomerInfo> customerInfoList = sqlMapper.selectList(sql11, CustomerInfo.class);
        for (CustomerInfo infos : customerInfoList) {
            infos.setOutpatientName(outpatientName);
            if (infos.getStartTime() != "" && infos.getStartTime() != null) {
                infos.setStartTimeOne(infos.getStartTime().substring(0, 10));
                infos.setStartTimeTwo(infos.getStartTime().substring(11, infos.getStartTime().length()));
            }
        }
        return customerInfoList;
    }

    public ArrearageRecordInfo billingRecords(String reservationsId) {
        //到过的店
        String abbreviationSql = "select abbreviation from distinction_customer_status where reservations_id = '" + reservationsId + "' ";
        List<String> abbreviationList = sqlMapper.selectList(abbreviationSql, String.class);
        String dealTables = "";
        String rechargeTables = "";
        for (int i = 0; i < abbreviationList.size(); i++) {
            String head = abbreviationList.get(i);
            if (i == abbreviationList.size() - 1) {
                dealTables += "select * from " + head + "deal_info where reservations_id='" + reservationsId + "' ";
                rechargeTables += "select document_number,deal_id,id2,recharge_people,operation_time,remarks from " + head + "recharge_info where reservations_id='" + reservationsId + "' ";
            } else {
                dealTables += "select * from " + head + "deal_info where reservations_id='" + reservationsId + "' union ";
                rechargeTables += "select document_number,deal_id,id2,recharge_people,operation_time,remarks from " + head + "recharge_info where reservations_id='" + reservationsId + "' union ";
            }
        }
        if (abbreviationList.size() == 0) {
            return null;
        }
        //账单记录
        Double totalServiceActualPay = 0.00;
        Double totalArrears = 0.00;
        String custDealSql = "select t1.id2 as id2," +
                "t1.reservations_id as reservationsId, " +
                "t1.outpatient_name as outpatientName," +
                "left(t1.operation_time,19) as operationTime," +
                "t1.deal_status as dealStatus," +
                "GROUP_CONCAT( distinct (t1.doctor)) as doctor,GROUP_CONCAT( distinct (t1.nurse)) as nurse," +
                "t1.tc_name as tcName," +
                "sum(t1.service_actual_pay+t1.arrears+t1.deduction_amount+t1.deposit_amount) AS foldingPay," +
                "sum(t1.service_actual_pay) as serviceActualPay," +
                "sum(t1.arrears) as arrears," +
                "sum(t1.deduction_amount) as deductionAmount," +
                "sum(t1.deposit_amount) as depositAmount," +
                "t1.remarks as  remarks,(select distinct(r1.document_number) from (" + rechargeTables + ") r1 where r1.deal_id=t1.id group by r1.deal_id) as documentNumber " +
                "from (" + dealTables + ") t1 where t1.deal_status='已结账' and  t1.reservations_id = '" + reservationsId + "' " +
                "group by t1.operation_time order by t1.operation_time desc";
        List<PayServicedInfo> custDealList = sqlMapper.selectList(custDealSql, PayServicedInfo.class);
        for (int i = 0; i < custDealList.size(); i++) {
            List<PayServicedInfo> dealList = getDealInfoList(custDealList.get(i).getOperationTime(), custDealList.get(i).getReservationsId());
            custDealList.get(i).setDealList(dealList);
            custDealList.get(i).setDocumentNumber(dealList.get(0).getDocumentNumber());
            totalServiceActualPay = totalServiceActualPay + custDealList.get(i).getServiceActualPay() + custDealList.get(i).getDepositAmount();
            totalArrears = totalArrears + custDealList.get(i).getArrears();
        }
        ArrearageRecordInfo arrearageRecordInfo = new ArrearageRecordInfo();
        arrearageRecordInfo.setArrearsDealList(custDealList);
        arrearageRecordInfo.setTotalPayDealActualPay(totalServiceActualPay);
        arrearageRecordInfo.setTotalPayDealArrears(totalArrears);
        return arrearageRecordInfo;
    }

    public ArrearageRecordInfo paymentRecords(String reservationsId) {
        //到过的店
        String abbreviationSql = "select abbreviation from distinction_customer_status where reservations_id = '" + reservationsId + "' ";
        List<String> abbreviationList = sqlMapper.selectList(abbreviationSql, String.class);
        String dealTables = "";
        String rechargeTables = "";
        for (int i = 0; i < abbreviationList.size(); i++) {
            String head = abbreviationList.get(i);
            if (i == abbreviationList.size() - 1) {
                dealTables += "select * from " + head + "deal_info where reservations_id='" + reservationsId + "' ";
                rechargeTables += "select document_number,deal_id,id2,recharge_people,operation_time,remarks from " + head + "recharge_info where reservations_id='" + reservationsId + "' ";
            } else {
                dealTables += "select * from " + head + "deal_info where reservations_id='" + reservationsId + "' union ";
                rechargeTables += "select document_number,deal_id,id2,recharge_people,operation_time,remarks from " + head + "recharge_info where reservations_id='" + reservationsId + "' union ";
            }
        }
        if (abbreviationList.size() == 0) {
            return null;
        }
        //支付记录
        String payDealSql = "select t.outpatient_name as outpatientName,left(t.get_time,19) as getTime,t.is_do as isDo,t.tc_name as tcName,t.deal_status as dealStatus,t2.recharge_people " +
                "as rechargePeople,sum(t.folding_pay) as foldingPay,sum(t.service_actual_pay) as serviceActualPay, sum(t.arrears) as arrears," +
                "(sum(t.folding_pay)+sum(t.arrears)) as tkfoldingPay,sum(t.deduction_amount) AS deductionAmount,sum(t.deposit_amount) as depositAmount," +   //退款=应收+欠费
                "t2.remarks as remarks from (" + dealTables + ") t left join (select b.id2 as id2, b.recharge_people as recharge_people, " +
                "b.operation_time as operation_time,b.remarks as remarks from (" + rechargeTables + ") b group by b.operation_time) t2 on t.get_time = t2.operation_time  where " +
                "t.reservations_id='" + reservationsId + "' and t.deal_status = '已结账' group by t.get_time order by t.get_time desc;";
        List<PayServicedInfo> payDealList = sqlMapper.selectList(payDealSql, PayServicedInfo.class);
        Double totalPayDealActualPay = 0.00;
        Double totalPayDealArrears = 0.00;
        for (int i = 0; i < payDealList.size(); i++) {
            totalPayDealActualPay = BigDecimal.valueOf(totalPayDealActualPay).add(BigDecimal.valueOf(payDealList.get(i).getServiceActualPay())).doubleValue();
            totalPayDealActualPay = BigDecimal.valueOf(totalPayDealActualPay).add(BigDecimal.valueOf(payDealList.get(i).getDepositAmount())).doubleValue();

            totalPayDealArrears = BigDecimal.valueOf(totalPayDealArrears).add(BigDecimal.valueOf(payDealList.get(i).getArrears())).doubleValue();
        }
        ArrearageRecordInfo arrearageRecordInfo = new ArrearageRecordInfo();
        arrearageRecordInfo.setArrearsDealList(payDealList);
        arrearageRecordInfo.setTotalPayDealActualPay(totalPayDealActualPay);
        arrearageRecordInfo.setTotalPayDealArrears(totalPayDealArrears);
        return arrearageRecordInfo;
    }

    public ArrearageRecordInfo arrearageRecord(String reservationsId, String areaAbbreviation, String proportional) {
        //到过的店
        String abbreviationSql = "select abbreviation from distinction_customer_status where reservations_id = '" + reservationsId + "' ";
        List<String> abbreviationList = sqlMapper.selectList(abbreviationSql, String.class);
        String dealTables = "";
        for (int i = 0; i < abbreviationList.size(); i++) {
            String head = abbreviationList.get(i);
            if (i == abbreviationList.size() - 1) {
                dealTables += "select * from " + head + "deal_info where reservations_id='" + reservationsId + "' ";
            } else {
                dealTables += "select * from " + head + "deal_info where reservations_id='" + reservationsId + "' union ";
            }
        }
        if (abbreviationList.size() == 0) {
            return null;
        }
        //欠费记录
        String arrearsDealSql = "select * from (select t.id2 as id2,t.reservations_id as reservationsId,t.customer_id as customerId," +
                " t.deal_status as dealStatus,t.outpatient_name as outpatientName,t.remarks as remarks,left(t.operation_time,19) as operationTime," +
                " t.doctor as doctor,t.tc_name as tcName,t.nurse as nurse,sum(t.deposit_amount) AS depositAmount,sum(t.deduction_amount) AS deductionAmount,sum(t.folding_pay) as foldingPay," +
                " sum(t.service_actual_pay)+sum(t.deposit_amount) as serviceActualPay,sum(t.arrears) as arrears,group_concat(distinct t1.pay_service_name) as payServiceName " +
                " from (" + dealTables + ") t  left join " + areaAbbreviation + "pay_service_info t1 on t.pay_service_id=t1.id where t.deal_status='已结账' group by t.operation_time ) t1 " +
                " where t1.reservationsId = '" + reservationsId + "' and t1.arrears  >0 order by t1.operationTime desc;";
        List<PayServicedInfo> arrearsDealList = sqlMapper.selectList(arrearsDealSql, PayServicedInfo.class);
        Double totalArrearsAmount = 0.00;
        for (int i = 0; i < arrearsDealList.size(); i++) {
            totalArrearsAmount += arrearsDealList.get(i).getArrears();
        }
        String getMoneyBalance = "select if(sum(a.actual_account_balance) is null,0,sum(a.actual_account_balance)) as moneyBalance from reservemoney_deal a " +
                " where a.reservations_id='" + reservationsId + "' limit 1";
        MembersUser membersUser1 = sqlMapper.selectOne(getMoneyBalance, MembersUser.class);
        String jfbalancesql1 = "select if(sum(score_balance) is null,0,sum(score_balance)) as scoreBalance from score_deal where reservations_id='" + reservationsId + "'";
        ScoreDealInfo scoreDealInfo1 = sqlMapper.selectOne(jfbalancesql1, ScoreDealInfo.class);
        String jfbalancesql = "select if(sum(score_balance) is null,0,sum(score_balance)) as scoreBalance from return_cash_info where reservations_id='" + reservationsId + "'";
        ScoreDealInfo scoreDealInfo = sqlMapper.selectOne(jfbalancesql, ScoreDealInfo.class);
        BigDecimal b1 = new BigDecimal(Double.parseDouble(proportional));
        BigDecimal b2 = new BigDecimal(Double.parseDouble(scoreDealInfo.getScoreBalance()));
        Double jfbalance = b1.multiply(b2).setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue();
        ArrearageRecordInfo arrearageRecordInfo = new ArrearageRecordInfo();
        arrearageRecordInfo.setArrearsDealList(arrearsDealList);
        arrearageRecordInfo.setTotalArrearsAmount(totalArrearsAmount);
        arrearageRecordInfo.setKhmoneyBalance(membersUser1.getMoneyBalance());
        arrearageRecordInfo.setJfbalancea(scoreDealInfo1.getScoreBalance());
        arrearageRecordInfo.setKhjfbalance(jfbalance);
        return arrearageRecordInfo;
    }

    public List<PayServicedInfo> getDealInfoList(String operationTime, String reservationsId) {
        if (ToolUtil.isEmpty(operationTime)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String abbreviationSql = "select abbreviation from distinction_customer_status where reservations_id = '" + reservationsId + "' and status=1 ";
        List<String> abbreviationList = sqlMapper.selectList(abbreviationSql, String.class);
        String dealTables = "";
        String rechargeTables = "";
        for (int i = 0; i < abbreviationList.size(); i++) {
            String head = abbreviationList.get(i);
            if (i == abbreviationList.size() - 1) {
                dealTables += "select * from " + head + "deal_info where reservations_id='" + reservationsId + "' ";
                rechargeTables += "select * from " + head + "recharge_info where reservations_id='" + reservationsId + "' ";
            } else {
                dealTables += "select * from " + head + "deal_info where reservations_id='" + reservationsId + "' union ";
                rechargeTables += "select * from " + head + "recharge_info where reservations_id='" + reservationsId + "' union ";
            }
        }
        String sql = "select t1.id as id,t1.outpatient_name as outpatientName,left(t1.get_time,19) as getTime,t1.is_do as isDo,t1.deal_status as dealStatus," +
                "t1.doctor1 as doctor,t1.nurse1 as nurse,t1.order_discount as orderDiscount,t1.deductionAmount as deductionAmount,t1.remarks as remarks,t1.serviceActualPay as serviceActualPay," +
                "t2.recharge_people as rechargePeople,t2.checkout_method as checkoutMethod," +
                "t2.document_number as documentNumber,t1.tc_name as tcName,left(t1.operation_time,19) as operationTime from (select t.id2 as id,t.outpatient_name as outpatient_name," +
                "t.get_time as get_time,t.tc_name as tc_name,t.is_do as is_do,t.deal_status as deal_status,GROUP_CONCAT(DISTINCT t.doctor) AS doctor1,GROUP_CONCAT(DISTINCT t.nurse) AS nurse1,(sum(t.service_actual_pay)+sum(t.deposit_amount)) as serviceActualPay," +
                "t.order_discount as order_discount,sum(t.deduction_amount) as deductionAmount,t.remarks as remarks,t.operation_time as operation_time from (" + dealTables + ") t where t.operation_time='" + operationTime + "' and t.reservations_id " +
                "= '" + reservationsId + "' and t.deal_status='已结账' group by t.get_time) t1 left join " + "(select b.recharge_people as recharge_people," +
                "b.document_number as  document_number,group_concat(distinct b.checkout_method) as checkout_method, b.operation_time as operation_time from (" + rechargeTables + ") b " +
                "group by b.operation_time) t2 on t1.get_time = t2.operation_time order by t1.get_time desc";
        List<PayServicedInfo> payServiceList = sqlMapper.selectList(sql, PayServicedInfo.class);
        return payServiceList;
    }

    public int insert(CustomerInfo info, String outpatientName, String userAccount) {
        info.setStatus("到店");
        info.setArrivalStore(outpatientName);
        if (info.getCustomerAge() != null) {
            for (int i = 0; i < 11; i++) {
                if (info.getCustomerAge() >= (i * 10) && info.getCustomerAge() <= (i * 10 + 9)) {
                    if (i < 10) {
                        String ageRange = (i * 10) + "-" + (i * 10 + 9);
                        info.setAgeRange(ageRange);
                    } else {
                        String ageRange = "99+";
                        info.setAgeRange(ageRange);
                    }
                }
            }
            if (info.getCustomerAge() >= 100) {
                info.setAgeRange("99+");
            }
        }
        info.setDepartment(info.getOneChannel());
        if ("null".equals(info.getHuifang()) || info.getHuifang() == null) {
            info.setHuifang("");
        }
        if ("null".equals(info.getWechatNumber()) || info.getWechatNumber() == null) {
            info.setWechatNumber("");
        }
        if ("null".equals(info.getAppointmentPayService()) || info.getAppointmentPayService() == null) {
            info.setAppointmentPayService("");
        }
        if ("null".equals(info.getFieldTc()) || info.getFieldTc() == null) {
            info.setFieldTc("");
        }
        if ("null".equals(info.getOtherContact()) || info.getOtherContact() == null) {
            info.setOtherContact("");
        }
        String sql = "INSERT INTO reservations_info (`id`,`huifang`,`department`,`appointment_time`,`tc_name`,`first_visit_date`,`age_range`,`appointment_pay_service`,`phone_shar_ship`,`outpatient_name`, `customer_name`, `customer_phone`, `customer_sex`, `customer_age`, `qq_number`," +
                " `wechat_number`,`other_contact`, `country`, `province`, `cities`, `region`, `quarters`," +
                " `marital_status`, `occupation`, `archiving`, `filing_time`, `entry_clerk`,`update_people`,`update_time`, " +
                " `one_medium`,`two_medium`,`thr_medium`, `one_channel`,`two_channel`,`thr_channel`,`remarks`, `status`,`appointment_arrival_time`," +
                "`is_supplement`,identification_number,arrival_store,szm,search_field,customer_sex_head) VALUES" +
                "('" + info.getReservationsId() + "','" + info.getHuifang() + "','" + info.getDepartment() + "','" + DateTimeKit.now() + "','" + info.getFieldTc() + "','" + DateTimeKit.now() + "','" + info.getAgeRange() + "','" + info.getAppointmentPayService() + "','" + info.getPhoneSharShip() + "','" + info.getArrivalStore() + "','" + info.getCustomerName() + "','" + info.getCustomerPhone() + "'," + info.getCustomerSex() + "," + info.getCustomerAge() + ",'" + info.getQqNumber() + "'," +
                "'" + info.getWechatNumber() + "','" + info.getOtherContact() + "','" + info.getCountry() + "','" + info.getProvince() + "','" + info.getCities() + "','" + info.getRegion() + "'," +
                "'" + info.getAddress() + "'," + info.getMaritalStatus() + ",'" + info.getOccupation() + "','" + info.getArchiving() + "','" + DateTimeKit.now() + "','" + userAccount + "','" + userAccount + "','" + DateTimeKit.now() + "'," +
                "'" + info.getOneMedium() + "','" + info.getTwoMedium() + "','" + info.getThrMedium() + "','" + info.getOneChannel() + "','" + info.getTwoChannel() + "','" + info.getThrChannel() + "','"
                + info.getRemarks() + "', '" + info.getStatus() + "','" + DateTimeKit.now() + "','否','" + info.getMedicalRecordNumber() + "','"
                + outpatientName + "',firstconcats('" + info.getCustomerName() + "')," +
                "concat('" + info.getCustomerName() + "','" + info.getCustomerPhone() + "',firstconcats('" + info.getCustomerName() + "')," +
                "'" + info.getCustomerPhone2() + "','" + info.getWechatNumber() + "','" + DateTimeKit.now() + "','" + info.getMedicalRecordNumber() + "'),'"
                + this.getSexPt(info) + "')";
        return sqlMapper.insert(sql);
    }

    //年龄获得头像
    public String getSexPt(CustomerInfo info) {
        Integer customerAge = info.getCustomerAge();
        Integer sex = info.getCustomerSex();
        if (StringUtil.isEmpty(customerAge)) {
            return "https://pynnn.oss-cn-hangzhou.aliyuncs.com/unkown-sex.png";
        } else if (customerAge >= 0 && customerAge < 18) {
            if (sex == 1) {
                return "https://pynnn.oss-cn-hangzhou.aliyuncs.com/nan-child.png";
            } else if (sex == 2) {
                return "https://pynnn.oss-cn-hangzhou.aliyuncs.com/nv-child.png";
            } else {
                return "https://pynnn.oss-cn-hangzhou.aliyuncs.com/unkown-sex.png";
            }
        } else if (customerAge >= 18 && customerAge <= 50) {
            if (sex == 1) {
                return "https://pynnn.oss-cn-hangzhou.aliyuncs.com/female-adult.png";
            } else if (sex == 2) {
                return "https://pynnn.oss-cn-hangzhou.aliyuncs.com/lfemale-child.png";
            } else {
                return "https://pynnn.oss-cn-hangzhou.aliyuncs.com/unkown-sex.png";
            }
        } else if (customerAge > 50) {
            if (sex == 1) {
                return "https://pynnn.oss-cn-hangzhou.aliyuncs.com/male-old.png";
            } else if (sex == 2) {
                return "https://pynnn.oss-cn-hangzhou.aliyuncs.com/female-old.png";
            } else {
                return "https://pynnn.oss-cn-hangzhou.aliyuncs.com/unkown-sex.png";
            }
        } else {
            return "https://pynnn.oss-cn-hangzhou.aliyuncs.com/unkown-sex.png";
        }
    }

    public List<DoctorRevisitInfo> FollowTableList(String reservationsId,String abbreviation){
        String sql = "select t.id as id,t.customer_id as customerId,t.reservations_id as reservationsId,t.revisit as revisit,t.revisit_time as revisitTime,t.outpatient_name as outpatientName,t.revisit_content as revisitContent,t.doctor as doctor,t.remarks as remarks," +
                "t.revisit_status as revisitStatus,t.create_name as createName,r.customer_name as customer_Name,r.arrival_store as arrivalStore,r.field_tc as fieldTc from return_doctor_visit_info t " +
                "left join " + abbreviation + "customer_info r on t.customer_id = r.id where t.reservations_id='" + reservationsId + "' order by t.revisit_time desc";
        List<DoctorRevisitInfo> list = sqlMapper.selectList(sql, DoctorRevisitInfo.class);
        return list;
    }
}